/*
 Navicat Premium Data Transfer

 Source Server         : localhost@SQL Server
 Source Server Type    : SQL Server
 Source Server Version : 15002000
 Source Host           : localhost:1433
 Source Catalog        : Pan
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 15002000
 File Encoding         : 65001

 Date: 30/04/2021 22:19:06
*/


-- ----------------------------
-- Table structure for files
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[files]') AND type IN ('U'))
	DROP TABLE [dbo].[files]
GO

CREATE TABLE [dbo].[files] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [uid] int  NOT NULL,
  [type] int  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [location] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [pid] int DEFAULT 0 NOT NULL,
  [code] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [createTime] datetime DEFAULT getdate() NOT NULL,
  [fsize] float(53)  NULL
)
GO

ALTER TABLE [dbo].[files] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'0 文件夹; 1 文件',
'SCHEMA', N'dbo',
'TABLE', N'files',
'COLUMN', N'type'
GO


-- ----------------------------
-- Records of files
-- ----------------------------
BEGIN TRANSACTION
GO

SET IDENTITY_INSERT [dbo].[files] ON
GO

SET IDENTITY_INSERT [dbo].[files] OFF
GO

COMMIT
GO


-- ----------------------------
-- Table structure for shares
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[shares]') AND type IN ('U'))
	DROP TABLE [dbo].[shares]
GO

CREATE TABLE [dbo].[shares] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [uid] int  NOT NULL,
  [fids] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [code] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [createTime] datetime DEFAULT getdate() NOT NULL
)
GO

ALTER TABLE [dbo].[shares] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of shares
-- ----------------------------
BEGIN TRANSACTION
GO

SET IDENTITY_INSERT [dbo].[shares] ON
GO

SET IDENTITY_INSERT [dbo].[shares] OFF
GO

COMMIT
GO


-- ----------------------------
-- Table structure for users
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type IN ('U'))
	DROP TABLE [dbo].[users]
GO

CREATE TABLE [dbo].[users] (
  [id] int  IDENTITY(1,1) NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [pwd] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [createTime] datetime DEFAULT getdate() NOT NULL
)
GO

ALTER TABLE [dbo].[users] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN TRANSACTION
GO

SET IDENTITY_INSERT [dbo].[users] ON
GO

SET IDENTITY_INSERT [dbo].[users] OFF
GO

COMMIT
GO


-- ----------------------------
-- procedure structure for DelFile
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DelFile]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[DelFile]
GO

CREATE PROCEDURE [dbo].[DelFile]
	@id int,
	@uid int
AS
BEGIN
	WITH _children AS
	(
	 SELECT f.id FROM files f WHERE f.pid=@id
			UNION ALL
	 SELECT f.id FROM _children,files f WHERE f.pid=_children.id
	)
	delete from files where id in (SELECT id FROM _children) and uid = @uid
	delete from files where id = @id and uid = @uid
END
GO


-- ----------------------------
-- procedure structure for DelShare
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DelShare]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[DelShare]
GO

CREATE PROCEDURE [dbo].[DelShare]
(
	@uid int,
	@code varchar(255)
)
AS
BEGIN
	delete from shares where uid = @uid and code = @code
END
GO


-- ----------------------------
-- procedure structure for GetFileByCode
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileByCode]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileByCode]
GO

CREATE PROCEDURE [dbo].[GetFileByCode]
	@code varchar(255)
AS
BEGIN
	select * from files where code = @code
END
GO


-- ----------------------------
-- procedure structure for GetFileById
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileById]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileById]
GO

CREATE PROCEDURE [dbo].[GetFileById]
	@id int
AS
BEGIN
	select * from files where id = @id
END
GO


-- ----------------------------
-- procedure structure for GetFileChild
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileChild]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileChild]
GO

CREATE PROCEDURE [dbo].[GetFileChild]
	@id int
AS
BEGIN
	WITH _children AS
	(
	 SELECT f.* FROM files f WHERE f.pid=@id
			UNION ALL
	 SELECT f.* FROM _children,files f WHERE f.pid=_children.id
	)
	SELECT * FROM _children
END
GO


-- ----------------------------
-- procedure structure for GetFileCount
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileCount]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileCount]
GO

CREATE PROCEDURE [dbo].[GetFileCount]
	@pid int
AS
BEGIN
	select count(*) 'count' from files where pid = @pid
END
GO


-- ----------------------------
-- procedure structure for GetFileList
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileList]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileList]
GO

CREATE PROCEDURE [dbo].[GetFileList]
	@pid int,
  @uid int
AS
BEGIN
	select * from files where pid = @pid and uid = @uid order by type,id
END
GO


-- ----------------------------
-- procedure structure for GetFileListByPage
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileListByPage]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileListByPage]
GO

CREATE PROCEDURE [dbo].[GetFileListByPage]
	@pid int,
  @uid int,
	@page int = 0
AS
BEGIN
	select * from files where pid = @pid and uid = @uid order by type,id offset (@page-1)*12 rows fetch next 12 rows only
END
GO


-- ----------------------------
-- procedure structure for GetFileParent
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFileParent]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetFileParent]
GO

CREATE PROCEDURE [dbo].[GetFileParent]
  @id int,
	@uid int
AS
BEGIN
	WITH _parent AS
	(
	 SELECT f.* FROM files f WHERE f.id=@id and f.type=0 and f.uid = @uid
			UNION ALL
	 SELECT f.* FROM _parent,files f WHERE f.id=_parent.pid and f.type=0 and f.uid = @uid
	)
	SELECT * FROM _parent order by id
END
GO


-- ----------------------------
-- procedure structure for GetShare
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetShare]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetShare]
GO

CREATE PROCEDURE [dbo].[GetShare]
	@code varchar(255)
AS
BEGIN
	select shares.*,users.name uname from shares,users where shares.code = @code and shares.uid = users.id
END
GO


-- ----------------------------
-- procedure structure for GetShareFile
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetShareFile]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetShareFile]
GO

CREATE PROCEDURE [dbo].[GetShareFile]
	@fids varchar(2000)
AS
BEGIN
	select * from files where id in(select res from [dbo].[Split](@fids, ','))
END
GO


-- ----------------------------
-- procedure structure for GetShareLst
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetShareLst]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetShareLst]
GO

CREATE PROCEDURE [dbo].[GetShareLst]
	@uid int
AS
BEGIN
	select * from shares where uid = @uid
END
GO


-- ----------------------------
-- procedure structure for Login
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Login]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[Login]
GO

CREATE PROCEDURE [dbo].[Login]
  @name varchar(255)
AS
BEGIN
	select users.id,users.name,users.pwd,files.id root
	from users,files
	where users.name = @name and files.uid = users.id and files.name = '根目录'
END
GO


-- ----------------------------
-- procedure structure for ModiPwd
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ModiPwd]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[ModiPwd]
GO

CREATE PROCEDURE [dbo].[ModiPwd]
	@uid int,
	@ori varchar(255),
	@new varchar(255)
AS
BEGIN
	update users set pwd = @new where id = @uid and pwd = @ori
END
GO


-- ----------------------------
-- procedure structure for NewFile
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[NewFile]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[NewFile]
GO

CREATE PROCEDURE [dbo].[NewFile]
  @uid int,
	@type int,
  @name varchar(255),
	@pid int,
	@code varchar(255),
	@fsize float = NULL,
	@location varchar(255) = NULL
AS
BEGIN
	insert into files(uid, type, name, pid, code, fsize, location) values(@uid, @type, @name, @pid, @code, @fsize, @location) 
END
GO


-- ----------------------------
-- procedure structure for Register
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Register]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[Register]
GO

CREATE PROCEDURE [dbo].[Register]
(
	@name varchar(255),
	@pwd varchar(255)
)
AS
BEGIN
	declare @uid int
	insert into users(name, pwd) values(@name, @pwd)
	set @uid = ident_current('users');
	insert into files(uid,type,name,code,pid) values(@uid, 0, '根目录', '', 0)
END
GO


-- ----------------------------
-- procedure structure for ShareFile
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ShareFile]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[ShareFile]
GO

CREATE PROCEDURE [dbo].[ShareFile]
(
	@uid int,
	@name varchar(255),
	@code varchar(255),
	@fids varchar(255)
)
AS
BEGIN
	insert into shares(uid, name, code, fids) values(@uid, @name, @code, @fids)
END
GO


-- ----------------------------
-- function structure for Split
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Split]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[Split]
GO

CREATE FUNCTION [dbo].[Split]
(
	@c varchar(2000),
	@split varchar(2)
)
RETURNS @t table(res varchar(20))
AS
BEGIN
	while(charindex(@split,@c)<>0)
		begin
			insert @t(res) values (substring(@c, 1, charindex(@split, @c)-1))
			set @c = stuff(@c, 1, charindex(@split, @c), '')
		end
	insert @t(res) values (@c)
	return
END
GO


-- ----------------------------
-- procedure structure for UserStat
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[UserStat]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[UserStat]
GO

CREATE PROCEDURE [dbo].[UserStat]
	@uid int
AS
BEGIN
	select users.createTime ct,sc,fc,dc from
	(select @uid as uid, count(*) as sc from shares where uid = @uid) as s
	left join 
	(select @uid as uid, count(case when files.type = 0 and files.name <> '根目录' then 1 else null end) as dc, count(case when files.type = 1 then 1 else null end) as fc from files where files.uid = @uid) as f on s.uid = f.uid
	left join users on users.id = @uid
END
GO


-- ----------------------------
-- Auto increment value for files
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table files
-- ----------------------------
ALTER TABLE [dbo].[files] ADD CONSTRAINT [PK__files__3213E83F64530799] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for shares
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table shares
-- ----------------------------
ALTER TABLE [dbo].[shares] ADD CONSTRAINT [PK__shares__3213E83FB05992AC] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for users
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table users
-- ----------------------------
ALTER TABLE [dbo].[users] ADD CONSTRAINT [PK__user__3213E83F2B9285F7] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

